import os
import pandas as pd
from openpyxl import load_workbook


def merge_excelfiles(dir_path, save_path):
    file_list = [os.path.join(dir_path, f) for f in os.listdir(dir_path) if f.endswith(".xlsx")]

    df_list = []
    headers = None

    for file in file_list:
        try:
            wb = load_workbook(file, data_only=True)
            for sheet_name in wb.sheetnames:
                ws = wb[sheet_name]
                data = list(ws.values)

                if not data:
                    print(f"⚠️ 跳过空文件或空Sheet: {file} - {sheet_name}")
                    continue

                if headers is None:
                    headers = list(data[0])
                    data_rows = data[1:]  # 去掉表头
                else:
                    if list(data[0]) == headers:
                        data_rows = data[1:]
                    else:
                        data_rows = data  # 有些 sheet 可能没有表头

                if not data_rows:
                    continue

                df = pd.DataFrame(data_rows, columns=headers)
                df_list.append(df)

        except Exception as e:
            print(f"❌ 读取文件失败: {file}，错误: {str(e)}")
            continue

    if df_list:
        merge_data = pd.concat(df_list, axis=0)
        merge_data.to_excel(save_path, index=False)
        print(f"✅ 合并完成，保存至: {save_path}")
    else:
        print("⚠️ 没有数据可以合并。")


# 示例路径
dir_path = "bosszhipin_spider"  # 设置Excel所在目录
save_path = "merged_后端开发.xlsx"  # 合并后的保存路径

merge_excelfiles(dir_path, save_path)
